package dao.impl;

import com.mysql.cj.util.StringUtils;
import dao.AdminDao;
import dao.BaseDao;
import entity.Admin;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class AdminDaoImpl extends BaseDao implements AdminDao {

    @Override
    public int getTotalCount(Integer id, String name) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            String sql = "SELECT COUNT(*) FROM users WHERE role='dormitory'";
            if (id != null) {
                sql += " AND user_id = ?";
            }
            if (!StringUtils.isNullOrEmpty(name)) {
                sql += " AND username LIKE ?";
            }

            ps = conn.prepareStatement(sql);
            int paramIndex = 1;
            if (id != null) {
                ps.setInt(paramIndex++, id);
            }
            if (!StringUtils.isNullOrEmpty(name)) {
                ps.setString(paramIndex++, "%" + name + "%");
            }

            rs = ps.executeQuery();
            return rs.next() ? rs.getInt(1) : 0;
        } finally {
            // 关闭资源（ResultSet → PreparedStatement → Connection）
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public List<Admin> chaxun(Integer id, String name, int page, int pageSize) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Admin> list = new ArrayList<>();

        try {

            String sql = "SELECT * FROM users WHERE role='dormitory'";
            if (id != null) {
                sql += " AND user_id = ?";
            }
            if (!StringUtils.isNullOrEmpty(name)) {
                sql += " AND username LIKE ?";
            }
            sql += " LIMIT ?, ?";

            ps = conn.prepareStatement(sql);
            int paramIndex = 1;
            if (id != null) {
                ps.setInt(paramIndex++, id);
            }
            if (!StringUtils.isNullOrEmpty(name)) {
                ps.setString(paramIndex++, "%" + name + "%");
            }
            ps.setInt(paramIndex++, page * pageSize);
            ps.setInt(paramIndex++, pageSize);

            rs = ps.executeQuery();
            while (rs.next()) {
                Admin admin = new Admin();
                admin.setUserId(rs.getInt("user_id"));
                admin.setUsername(rs.getString("username"));
                admin.setPhone(rs.getString("phone"));
                admin.setSex(rs.getString("sex"));
                list.add(admin);
            }
            return list;
        } finally {
            // 关闭资源
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public int tianjia(Admin admin) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement ps = null;

        try {

            String sql = "INSERT INTO users(username,phone,sex,password,role) VALUES(?,?,?,?,'dormitory')";
            ps = conn.prepareStatement(sql);
            ps.setString(1, admin.getUsername());
            ps.setString(2, admin.getPhone());
            ps.setString(3, admin.getSex());
            ps.setString(4, admin.getPassword());

            return ps.executeUpdate();
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public int xiugai(Admin admin) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement ps = null;

        try {

            String sql = "UPDATE users SET username=?,phone=?,sex=? WHERE user_id=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, admin.getUsername());
            ps.setString(2, admin.getPhone());
            ps.setString(3, admin.getSex());
            ps.setInt(4, admin.getUserId());

            return ps.executeUpdate();
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public int shanchu(Integer id) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement ps = null;

        try {

            String sql = "DELETE FROM users WHERE user_id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);

            return ps.executeUpdate();
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}